<?php 
class Monthly_feeController extends Controller{
	private $result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());	
	public $mainMenu = "Finance";
	public $subMenu = "";
	public $title = "";
	public $loginUser;
	
	public function init() {
		$this->loginUser = LoginUser::getUser();	
	}
	
	public function actionIndex(){
		$this->subMenu = "Student Fee";
		$this->layout ="//layouts/ly_blank";
			
		$departments = MDepartment::model()->findAll("is_active = TRUE order by id");
		$department_id ="";
			
		if ($departments == null){
			$department_id == "";
		}else{
			$department_id = $department_id == "" ? $departments[0]->id : $department_id;
		}
			
		$tahunAjarans = MTahunAjaran::model()->findAll("department_id=:departmentId and is_active = TRUE order by id",array(":departmentId"=>$department_id));
		$tahun_ajaran_id = "";
		if ($tahunAjarans == null){
			$tahun_ajaran_id == "";
		}else{
			$tahun_ajaran_id = $tahun_ajaran_id == "" ? $tahunAjarans[0]->id : $tahun_ajaran_id;
		}
		
		
		$criteria=new CDbCriteria();
		//$criteria->select ="student_id,kelas_tahun_ajaran_id";
		//$criteria->condition = 'kelasTahunAjaran.tahun_ajaran_id =:tahunAjaranId';
		$criteria->condition = 'tahun_ajaran_id =:tahunAjaranId and is_active = 1';
		//$criteria->group = 'kelasTahunAjaran.kelas_id';		
		$criteria->params = array(":tahunAjaranId"=>$tahun_ajaran_id);
		
		//$arrKelasTahunAjaran = MStudentClass::model()->with("kelasTahunAjaran")->findAll($criteria);
		$arrKelasTahunAjaran = MKelasTahunAjaran::model()->findAll($criteria);
		$kelas_tahun_ajaran_id = "";
		if ($arrKelasTahunAjaran != null){
			$kelas_tahun_ajaran_id  = $kelas_tahun_ajaran_id == "" ? $arrKelasTahunAjaran[0]->id : $kelas_tahun_ajaran_id;
		}
		
		
		
			
		$data= array(
				"departments" => $departments,
				"department_id"=>$department_id,
				"tahunAjarans"=>$tahunAjarans,
				"tahun_ajaran_id" => $tahun_ajaran_id,
				"arrKelas"=>$arrKelasTahunAjaran,
				"kelas_tahun_ajaran_id"=>$kelas_tahun_ajaran_id,				
		);
		
		$this->render('index',$data);
	}
	
	public function actiondetailList(){
		$kelas_tahun_ajaran_id = Yii::app()->request->getParam("kelas_tahun_ajaran_id","");
		$rs = MStudentClass::model()->findAll("kelas_tahun_ajaran_id = :kelasTahunAjaranId",array(":kelasTahunAjaranId"=>$kelas_tahun_ajaran_id));
		$arrRs = CJSON::decode(CJSON::encode($rs));
		for ($i=0;$i < sizeof($arrRs);$i++){
			$arrRs[$i]["sid"] = $rs[$i]->student->sid;			
			$arrRs[$i]["name"] = $rs[$i]->student->name;
		}		
		$this->result["actionStatus"] =0;
		$this->result["actionMessage"] ="";
		$this->result["actionError"] =array();
		$this->result["model"] = $arrRs;			
		$this->returnJSON($this->result);
		
	}
	public function actionform(){		
		$id = Yii::app()->request->getParam("id","");
		$studentClass = MStudentClass::model()->findByPk($id);		
		$data["studentClass"] = $studentClass;
		$this->render('form',$data);				
	}
	
	public function actionformGenerateMF(){
		$data= array();
		$period = Yii::app()->request->getParam("period","");
		$department_id = Yii::app()->request->getParam("department_id","");
		$payment_no = "";
		//$tingkatan_id = Yii::app()->request->getParam("tingkatan_id","");
		//$tingkatan = MTingkatan::model()->findByPK($tingkatan_id);		
		//$kelas_tahun_ajaran_id = Yii::app()->request->getParam("kelas_tahun_ajaran_id","");
		//$kelasTahunAjaran = MKelasTahunAjaran::model()->findByPK($kelas_tahun_ajaran_id);
		
		//$arrStudentClass = MStudentClass::model()->findAll("kelas_tahun_ajaran_id =:kelasTahunAjaranId and is_active = 1",array(":kelasTahunAjaranId"=>$kelas_tahun_ajaran_id));
		$arrStudentClass = MStudentClass::model()->with(array(
				"kelasTahunAjaran"=>array("alias"=>"a"),
				"kelasTahunAjaran.kelas"=>array("alias"=>"b"),
				"kelasTahunAjaran.kelas.tingkatan"=>array("alias"=>"c"),
				"kelasTahunAjaran.kelas.tingkatan.department"=>array("alias"=>"d"),
				"kelasTahunAjaran.tahunAjaran"=>array("alias"=>"e"),
			))->findAll("c.department_id =:deptId and 
				t.is_active = 1 and a.is_active = 1 and b.is_active = 1 and c.is_active = 1 and d.is_active = 1 and e.is_active = 1 and
				e.start_date <= :startPeriod and e.end_date >= :startPeriod",
				array(":deptId"=>$department_id,":startPeriod"=>Utils::getPeriodStartDate($period)));
		
		$op = Yii::app()->request->getParam("op","");
		
		if ($op == "add"){
			$data["id"] = "";
			$data["op"] = "add";
			$data["period"] = $period;
			$data["department_id"] = $department_id;
			$data["arrStudentClass"] =$arrStudentClass;
			$data["student_class_id"] ="";
			$data["student_name"] ="";
			$data["tingkatan_name"] ="";
			$data["kelas_name"] ="";
			$data["monthly_fee"] =0;			
			$data["disc_monthly_fee"] =0;
			$data["start_period"] =$period;
			$data["end_period"] =$period;
			$data["ttl_monthly_fee"] =0;
			$data["ttl_late_fee"] =0;
			$data["ttl_disc_fee"] =0;
			$data["ttl_paid"] =0;
			$data["ttl_cn"] =0;
			$data["payment_type"] = "";
		}else if($op == "edit"){
			$id = Yii::app()->request->getParam("id","");
			$studentMonthlyFee = MStudentMonthlyFee::model()->findByPK($id);
			$data["id"] = $id;
			$data["op"] = "edit";
			$data["period"] = $period;
			$data["department_id"] = $department_id;
			$data["arrStudentClass"] =$arrStudentClass;
			$data["student_class_id"] =$studentMonthlyFee->student_class_id; 
			$data["student_name"] =$studentMonthlyFee->studentClass->student->sid ." - ". $studentMonthlyFee->studentClass->student->name;
			$data["tingkatan_name"] =$studentMonthlyFee->studentClass->kelasTahunAjaran->kelas->tingkatan->name;
			$data["kelas_name"] =$studentMonthlyFee->studentClass->kelasTahunAjaran->kelas->name;
			$data["monthly_fee"] =$studentMonthlyFee->studentClass->monthly_fee;
			$data["disc_monthly_fee"] =$studentMonthlyFee->studentClass->disc_monthly_fee;
			$data["start_period"] =$studentMonthlyFee->start_period;
			$data["end_period"] =$studentMonthlyFee->end_period;
			$data["ttl_monthly_fee"] =$studentMonthlyFee->ttl_monthly_fee;
			$data["ttl_disc_fee"] =$studentMonthlyFee->ttl_disc_fee;
			$data["ttl_late_fee"] =$studentMonthlyFee->ttl_late_fee;
			$data["ttl_paid"] = $studentMonthlyFee->studentInvoice->ttl_paid;
			$data["ttl_cn"] = $studentMonthlyFee->studentInvoice->ttl_cn;
			$data["payment_type"] = $studentMonthlyFee->studentInvoice->payment_type;
		}
		$this->render('formGenerateMF',$data);
	}
	
	
	public function actionedit(){
		$id = Yii::app()->request->getParam("id","");
		$studentClass = MStudentClass::model()->findByPk($id);
				
		if ($studentClass != null){
			$monthly_fee = Yii::app()->request->getParam("monthly_fee",0);
			$disc_monthly_fee = Yii::app()->request->getParam("disc_monthly_fee",0);
			$monthly_fee = Utils::parseCurrency($monthly_fee);
			$disc_monthly_fee = Utils::parseCurrency($disc_monthly_fee);
			$is_active = Yii::app()->request->getParam("is_active",0);
			
			$is_active = $is_active === 0 ? 0 :1;
			 
			$studentClass->monthly_fee =$monthly_fee;  
			$studentClass->disc_monthly_fee =$disc_monthly_fee;
			
			
			if ($is_active == 1 && $studentClass->is_active == 0){				
				//rubah status dari tidak aktif menjadi aktif
				//1. Cek apakah siswa tsb telah terdaftar di kelas lain atau tidak untuk tahun ajaran tersebut
				$cekStudentClass = MStudentClass::model()->with("kelasTahunAjaran")->findAll("student_id = :studentId and kelasTahunAjaran.tahun_ajaran_id = :tahunAjaranId",array(":studentId"=>$studentClass->student_id,":tahunAjaranId"=>$studentClass->kelasTahunAjaran->tahun_ajaran_id));
				if ($cekStudentClass != null){
					//print_r($cekStudentClass->kelasTahunAjaran);
					
					//exit;
					echo "<script>
							alert('".Yii::t("dialog","Siswa telah terdaftar di kelas " . $cekStudentClass[0]->kelasTahunAjaran->kelas->name) . "');
							window.close();
						</script>";
					exit;
				}
			} 
			
			$studentClass->is_active = $is_active;
			
			if ($studentClass->save()){
				echo "<script>
							alert('".Yii::t("dialog","Perubahan data sudah di simpan...!."). "');
							window.close();
						</script>";
				exit;
			}else{
				print_r($studentClass->getErrors());
			}
		}else{
			echo "<script>alert('".Yii::t("dialog","data tidak di temukan!"). "');window.close();</script>";
			exit;
		}		
	}
	
	public function actioneditGenerateMF(){
		$op = Yii::app()->request->getParam("op","");
		$id = Yii::app()->request->getParam("id","");		
		if ($op == "add"){
			$errorMessage = Yii::t("label","Terjadi kesalahan, data tidak dapat di tambahkan.");
			
			$transaction = Yii::app()->db->beginTransaction();			
			try{
				$student_class_id = Yii::app()->request->getParam("student_class_id","");
				$startPeriod = Yii::app()->request->getParam("start_period","");
				$endPeriod = Yii::app()->request->getParam("end_period","");
				$ttlMonthlyFee = Yii::app()->request->getParam("ttl_monthly_fee",0);
				$ttlMonthlyFee = Utils::parseCurrency($ttlMonthlyFee);
				$ttlLateFee = Yii::app()->request->getParam("ttl_late_fee",0);
				$ttlLateFee = Utils::parseCurrency($ttlLateFee);
				$ttlDiscFee = Yii::app()->request->getParam("ttl_disc_fee",0);
				$ttlDiscFee = Utils::parseCurrency($ttlDiscFee);
								
				if (Utils::isValidPeriod($startPeriod) && Utils::isValidPeriod($endPeriod)){					
				}else{
					$errorMessage = "Period mulai ($startPeriod) atau period akhir ($endPeriod) tidak valid"; 
					throw new Exception("Invalid period format");
				}
					
				$studentClass =MStudentClass::model()->findByPK($student_class_id);
				if ($studentClass == null){
					$errorMessage = Yii::t("dialog","Siswa tidak di kenal");
					throw new Exception("Invalid student");
					
				}
				
				//cek period sudah ada
				$studentMFCek = MStudentMonthlyFee::model()->find("student_class_id =:studentClassId and 
						(start_period between :startPeriod and :endPeriod or end_period between :startPeriod and :endPeriod or 
						(start_period >= :startPeriod and end_period <= :endPeriod) or (start_period <= :startPeriod and end_period >= :endPeriod))",
						array(":studentClassId"=>$student_class_id,":startPeriod"=>$startPeriod ,":endPeriod"=>$endPeriod));
				
				if ($studentMFCek != null){
					$errorMessage = "Monthly Fee period :$studentMFCek->start_period - $studentMFCek->end_period untuk ". $studentClass->student->name . " sudah ada ";
					throw new Exception("Monthly fee already exist");
				} 
				
						
	
				// Jurnal Header			
				$notes = "Monthly Fee Period : " .$startPeriod ." - " .$endPeriod ."(" . $studentClass->student->sid ." - " . $studentClass->student->name  .")";
				$temp = Jurnal::createHeader($notes, date("Y-m-d"));
				$jurnalId = $temp["id"];
				
				//create student header invoice
				$studentInv = new MStudentHInvoice();
				$studentInv->invoice_no = AutoNumber::getNewStudentInvoiceNo();
				$studentInv->student_transaction_type_id = 1;			
				$studentInv->invoice_date = Utils::getPeriodStartDate($startPeriod);
				$studentInv->student_id = $studentClass->student_id;			
				$studentInv->notes = "Monthly Fee Period : " .$startPeriod ." - " .$endPeriod;
				$studentInv->ttl_invoice = $ttlMonthlyFee + $ttlLateFee;
				$studentInv->ttl_disc =  $ttlDiscFee;
				$studentInv->ttl_paid =  0;
				$studentInv->ttl_cn =  0;
				$studentInv->invoice_type = "Auto";
				$studentInv->payment_type = $studentClass->student->payment_method;
				$studentInv->jurnal_id =  $jurnalId;
				$studentTransactionType = MStudentTransactionType::model()->findByPK(1);
				$studentInv->piutang_acc_id =  $studentTransactionType->piutang_acc_id;
				$studentInv->input_date_time = date("Y-m-d H:i:s");
				$studentInv->user_id = LoginUser::getUser()->id;
				$studentInv->is_active = 1;	
						
				if ($studentInv->save()){	
					//create student detail invoice monthly fee
					$studentDInv = new MStudentDInvoice();
					$studentDInv->invoice_id = $studentInv->id;
					$studentDInv->student_item_id = 1;
					$studentItem = MStudentItem::model()->findByPK(1);
					$studentDInv->total = $ttlMonthlyFee;
					$studentDInv->disc = $ttlDiscFee;
					$studentDInv->disc_acc_id = $studentItem->disc_acc_id;
					$studentDInv->pendapatan_acc_id = $studentItem->pendapatan_acc_id;
					if(!$studentDInv->save()){
						throw new Exception(json_encode($studentDInv->getErrors()));
					}
					//Jurnal Detail MF
					$tmp = Jurnal::createDetail($jurnalId, $studentInv->piutang_acc_id, "", "D", $ttlMonthlyFee -$ttlDiscFee);
					$tmp = Jurnal::createDetail($jurnalId, $studentDInv->disc_acc_id, "", "D", $ttlDiscFee);
					$tmp = Jurnal::createDetail($jurnalId, $studentDInv->pendapatan_acc_id, "", "C", $ttlMonthlyFee);
					
					//create student detail invoice late fee
					if ($ttlLateFee > 0){
						$studentDInv = new MStudentDInvoice();
						$studentDInv->invoice_id = $studentInv->id;
						$studentDInv->student_item_id = 2;
						$studentItem = MStudentItem::model()->findByPK(1);
						
						$studentDInv->total = $ttlLateFee;
						$studentDInv->disc = 0;
						$studentDInv->disc_acc_id = $studentItem->disc_acc_id;
						$studentDInv->pendapatan_acc_id = $studentItem->pendapatan_acc_id;
						if(!$studentDInv->save()){
							throw new Exception(json_encode($studentDInv->getErrors()));
						}
						//Jurnal Detail Late Fee
						$tmp = Jurnal::createDetail($jurnalId, $studentInv->piutang_acc_id, "", "D", $ttlLateFee);
						$tmp = Jurnal::createDetail($jurnalId, $studentDInv->pendapatan_acc_id, "", "C", $ttlLateFee);
					}
					//create student monthly fee
					$studentMonthlyFee = new MStudentMonthlyFee();
					$studentMonthlyFee->student_class_id=$student_class_id;
					$studentMonthlyFee->ttl_monthly_fee=$ttlMonthlyFee;
					$studentMonthlyFee->ttl_late_fee=$ttlLateFee;
					$studentMonthlyFee->ttl_disc_fee=$ttlDiscFee;
					$studentMonthlyFee->start_period=$startPeriod;
					$studentMonthlyFee->end_period=$endPeriod;
					$studentMonthlyFee->student_invoice_id=$studentInv->id;
					if (!$studentMonthlyFee->save()){
						throw new Exception(json_encode($studentMonthlyFee->getErrors()));
					}
					$transaction->commit();
					echo "<script>alert('".Yii::t("dialog","Data Telah di simpan !") . "');window.close();</script>";
				}else{
					throw new Exception(json_encode($studentInv->getErrors()));
				}
			}catch (Exception $e){				
				$transaction->rollback();
				$this->pageError($errorMessage,$e->getMessage(),$e);
			}
			
		}else if($op == "edit"){
			$errorMessage=Yii::t("label","Perubahan data gagal !");
			$conn = Yii::app()->db;
			$transaction = $conn->beginTransaction();
			
			try{
				$student_class_id = Yii::app()->request->getParam("student_class_id","");
				$startPeriod = Yii::app()->request->getParam("start_period","");
				$endPeriod = Yii::app()->request->getParam("end_period","");
				$ttlMonthlyFee = Yii::app()->request->getParam("ttl_monthly_fee",0);
				$ttlMonthlyFee = Utils::parseCurrency($ttlMonthlyFee);
				$ttlLateFee = Yii::app()->request->getParam("ttl_late_fee",0);
				$ttlLateFee = Utils::parseCurrency($ttlLateFee);
				$ttlDiscFee = Yii::app()->request->getParam("ttl_disc_fee",0);
				$ttlDiscFee = Utils::parseCurrency($ttlDiscFee);
				$paymentType = Yii::app()->request->getParam("payment_type","BCA VA");
				$studentMF = MStudentMonthlyFee::model()->findByPK($id);
				$studentHInv = MStudentHinvoice::model()->find("id = :invId and is_active = 1",array(":invId"=>$studentMF->student_invoice_id));
				if ($studentHInv == null){
					throw new Exception("Invoice ID (" . $studentMF->student_invoice_id .") not found !");
				}
				if (($studentHInv->ttl_paid +  $studentHInv->ttl_cn) > 0){
					$errorMessage = Yii::t("label","Data tidak dapat di rubah, karena invoice telah di bayarkan");
					throw new Exception("Invoice already paid");
				}
				if ($student_class_id != $studentMF->student_class_id){
					throw new Exception("Invalid student class id, student class id has been changed !");
				}
				
				//cek period sudah ada
				$studentMFCek = MStudentMonthlyFee::model()->find("student_class_id =:studentClassId and id != :id and 
						(start_period between :startPeriod and :endPeriod or end_period between :startPeriod and :endPeriod or
						(start_period >= :startPeriod and end_period <= :endPeriod) or (start_period <= :startPeriod and end_period >= :endPeriod))",
						array(":studentClassId"=>$student_class_id,":startPeriod"=>$startPeriod ,":endPeriod"=>$endPeriod,":id"=>$id));
					
				if ($studentMFCek != null){
					throw new Exception("Invalid Start / Stop Period");
				}
				
				//Process Update MF Detail
				$studentDInv = MStudentDinvoice::model()->find("invoice_id =:invId and student_item_id = 1",array(":invId"=>$studentHInv->id));
				if (!$studentDInv == null){
					Jurnal::deleteDetailJurnal($studentHInv->jurnal_id,$conn);
					$tmp = Jurnal::createDetail($studentHInv->jurnal_id, $studentHInv->piutang_acc_id, "", "D",$ttlMonthlyFee - $ttlDiscFee);
					$tmp = Jurnal::createDetail($studentHInv->jurnal_id, $studentDInv->disc_acc_id, "", "D",$ttlDiscFee);
					$tmp = Jurnal::createDetail($studentHInv->jurnal_id, $studentDInv->pendapatan_acc_id, "", "C",$ttlMonthlyFee);
					
					$studentDInv->total = $ttlMonthlyFee;
					$studentDInv->disc = $ttlDiscFee;
					if(!$studentDInv->save()){
						throw new Exception(json_encode($studentDInv->getErrors()));
					}
				}
				
				//Process Update Late Charge Detail
				$studentDInv = MStudentDinvoice::model()->find("invoice_id =:invId and student_item_id = 2",array(":invId"=>$studentHInv->id));
				
				//transaksi sebelumnya ada late feenya
				if ($studentDInv != null){
					Jurnal::deleteDetailJurnal($studentHInv->jurnal_id,$conn);
				}
				
				if($ttlLateFee ==0){
					if ($studentDInv != null){
						if(!$studentDInv->delete()){
							throw new Exception(json_encode(json_encode($studentDInv->getErrors())));
						}
					}
										
				}else{
					if ($studentDInv == null){
						$studentDInv = new MStudentDinvoice();
						$studentDInv->invoice_id = $studentMF->student_invoice_id;
						$studentDInv->student_item_id = 2;
						$studentDInv->notes = "Late Fee";
						$studentDInv->qty = 1;
						$studentDInv->total = $ttlLateFee;
						$studentDInv->disc = 0;
						$studentItem = MStudentItem::model()->findByPK(2);						
						$studentDInv->disc_acc_id = $studentItem->jurnalSetting->disc_acc_id;
						$studentDInv->pendapatan_acc_id = $studentItem->jurnalSetting->pendapatan_acc_id;
					}
										
					$tmp = Jurnal::createDetail($studentHInv->jurnal_id, $studentHInv->piutang_acc_id, "", "D",$ttlLateFee);
					$tmp = Jurnal::createDetail($studentHInv->jurnal_id, $studentDInv->pendapatan_acc_id, "", "C",$ttlLateFee);
					$studentDInv->total = $ttlLateFee;
					$studentDInv->disc = 0;
					if(!$studentDInv->save()){
						throw new Exception(json_encode($studentDInv->getErrors()));
					}
				}
				
				$studentHInv->ttl_invoice = $ttlMonthlyFee + $ttlLateFee;
				$studentHInv->ttl_disc = $ttlDiscFee;
				$studentHInv->payment_type = $paymentType;
				$studentHInv->input_date_time = date("Y-m-d H:i:s");					
				$studentHInv->user_id =  $this->loginUser->id;
				if(!$studentHInv->save()){
					throw new Exception(json_encode($studentHInv->getErrors()));
				}
				 
				$studentMF->ttl_monthly_fee = $ttlMonthlyFee;
				$studentMF->ttl_disc_fee = $ttlDiscFee;
				$studentMF->ttl_late_fee = $ttlLateFee;
				$studentMF->start_period = $startPeriod;
				$studentMF->end_period = $endPeriod;					
				if(!$studentMF->save()){
					throw new Exception(json_encode($studentMF->getErrors()));
				}
				$transaction->commit();
				echo "<script>alert('".Yii::t("dialog","Data Telah di simpan !") . "');window.close();</script>";
			
			}catch (Exception $e){
				$transaction->rollback();
				$this->pageError($errorMessage,$e->getMessage(),$e);
			}
		}else if($op == "del"){
			/*
			 * 1. hapus detail jurnal & set hjurnal is_active =0
			 * 2. hapus record student monthly fee
			 * 3. set invoice student is_active = 0
			 */
			
			//$this->result["actionStatus"] =2;
			//$this->result["actionMessage"] =Yii::t("dialog","Data gagal di hapus!");
			$errorMessage = Yii::t("dialog","Data gagal di hapus!");			
			$studentMonthlyFee = MStudentMonthlyFee::model()->findByPK($id);
			if ($studentMonthlyFee == null){
				$this->result["actionError"] ="";
				$this->returnJSON($this->result);
			} 
			
			$conn = Yii::app()->db;
			$transaction = $conn->beginTransaction();
			
			try{
				$studentHInv =$studentMonthlyFee->studentInvoice;
				if ($studentHInv == null){
					$this->result["actionStatus"] =2;
					$this->result["actionMessage"] =$errorMessage;
					$this->result["actionError"] =$studentHInv->getErrors();
					$this->result["actionError"]["model"] ="student Header invoice ";
					$this->returnJSON($this->result);
				} 
				//cek apakah invoice sudah di bayar
				if (($studentHInv->ttl_paid + $studentHInv->ttl_cn)  != 0 ){
					$this->result["actionStatus"] =2;
					$this->result["actionMessage"] =Yii::t("label","Data tidak dapat di hapus karena sudah ada pembayaran!");
					$this->returnJSON($this->result);
				}
				
				$hJurnal = MHjurnal::model()->findByPK($studentHInv->jurnal_id);
				
				//delete detail jurnal
				Jurnal::deleteDetailJurnal($hJurnal->id,$conn);
				
				//delete detail invoice
				$ssql = "delete from m_student_dinvoice where invoice_id = :invoiceId";
				$cmd = $conn->createCommand($ssql);
				$cmd->execute(array(":invoiceId"=>$studentHInv->id));
				
				//delete student_monthly_fee
				if(!$studentMonthlyFee->delete()){
					$this->result["actionStatus"] =2;
					$this->result["actionMessage"] =$errorMessage;
					$this->result["actionError"] =$studentMonthlyFee->getErrors();
					$this->result["actionError"]["model"] ="student Monthly fee";
					$transaction->rollback();
					$this->returnJSON($this->result);
				}
				
				//delete header invoice
				if (!$studentHInv->delete()){
					$this->result["actionStatus"] =2;
					$this->result["actionMessage"] =$errorMessage;
					$this->result["actionError"] =$studentHInv->getErrors();
					$this->result["actionError"]["model"] ="Student Header Invoice";
					$transaction->rollback();
					$this->returnJSON($this->result);
				}
				
				if (!$hJurnal->delete()){
					$this->result["actionStatus"] =2;
					$this->result["actionMessage"] =$errorMessage;
					$this->result["actionError"] =$hJurnal->getErrors();
					$this->result["actionError"]["model"] ="Student Header Jurnal";
					$transaction->rollback();
					$this->returnJSON($this->result);
				}
			}catch (Exception $e){
				$transaction->rollback();
				$this->result["actionStatus"] =2;
				$this->result["actionMessage"] =$errorMessage;
				$this->result["actionError"] =$e->getMessage();
				$this->returnJSON($this->result);
			}
							
			$transaction->commit();
			$this->result["actionStatus"] =0;
			$this->result["actionMessage"] =Yii::t("dialog","Hapus data success!");
			$this->returnJSON($this->result);
			
		}		
	}
	
	public function actionGenerate(){
		set_time_limit(0);
		$period = Yii::app()->request->getParam("period","");
		$errorMessage = Yii::t("dialog","Proses generate monthy fee gagal !");
		
		if (!Utils::isValidPeriod($period)){
			$this->result["actionStatus"] =2;
			$this->result["actionMessage"] =Yii::t("dialog","Invalid period $period process di batalkan.");
			$this->result["actionError"] =Array("Invalid Period $period");
			$this->result["actionError"]["model"] = "Period";
			$this->returnJSON($this->result);
		}
				
		$startDate = Utils::getPeriodStartDate($period);
	
		//get all active student
		$rsStudentClass = MStudentClass::model()
			->with(array(
		  			"kelasTahunAjaran"=>array('alias'=>'c'),
		  			"kelasTahunAjaran.tahunAjaran"=>array('alias'=>'a'),
		  			"kelasTahunAjaran.tahunAjaran.department"=>array('alias'=>'d'),
		  			"kelasTahunAjaran.kelas"=>array('alias'=>'e'),
		  			"kelasTahunAjaran.kelas.tingkatan"=>array('alias'=>'f'),
		  			"student"=>array("alias"=>"b")		  			
		  	))->findAll(
		  		"a.start_date <=:startDate and a.end_date >=:startDate and 
		  		a.is_active = 1 and	b.is_active = 1 and c.is_active = 1 and	d.is_active = 1 and	e.is_active = 1 and	f.is_active = 1 and	t.is_active = 1",
		  		array(":startDate"=>$startDate)
		  	);
		// id 1 default untuk monthly fee sysdata = true data tidak dapat di hapus;
		// id 2 default untuk late fee sysdata = true data tidak dapat di hapus;
					
		$transaction = Yii::app()->db->beginTransaction();
		try{
			if ($rsStudentClass == null){
				$errorMessage ="Tidak ada data yang tersedia";
				throw new Exception("No Reord found !");
			}
			
			//create bulk no process
			$bulkNo = AutoNumber::getMonthlyFeeBulkNo();
			$invoiceNo = AutoNumber::getNewStudentInvoiceNo();
			$ttlMonthlyFee = 0;
			$ttlDiscMonthlyFee = 0;
			$ttlLateMonthlyFee = 0;
							
			foreach ($rsStudentClass as $studentClass){
				//cek Last monthly fee (student_monthly_fee)			
				$studentMonthlyFee = MStudentMonthlyFee::model()->findAll("student_class_id = :studentClassId and start_period <= :period and end_period >= :period",array(":studentClassId" =>$studentClass->id,":period"=>$period));
			
				if ($studentMonthlyFee == null){
					
					
					//create Jurnal header untuk monthly fee
					$notes = "Monthly Fee $period (" .$studentClass->student->sid . " - " . $studentClass->student->name .")";
					$tmp = Jurnal::createHeader($notes, date("Y-m-d"));
					$jurnalId = $tmp["id"];
														
					//create invoice header								
					$studentInv = new MStudentHinvoice();
					$studentInv->invoice_no = $invoiceNo;
					$studentInv->student_transaction_type_id = 1; // sys_data for monthly fee 
					$studentInv->bulk_no = $bulkNo;
					$studentInv->invoice_date = $startDate;
					$studentInv->student_id = $studentClass->student_id;		
					$studentInv->notes = "Monthly Fee Period : " .$period;
					$studentInv->payment_type = $studentClass->student->payment_method;
					$studentInv->jurnal_id = $jurnalId;				
					$studentTransactionType = MStudentTransactionType::model()->findByPK(1);				
					$studentInv->piutang_acc_id =$studentTransactionType->piutang_acc_id; 
					$studentInv->ttl_invoice = 0;
					$studentInv->ttl_disc =  0;
					$studentInv->ttl_paid =  0;		
					$studentInv->ttl_cn =  0;
					$studentInv->invoice_type = 'Auto';
					$studentInv->input_date_time = date("Y-m-d H:i:s");
					$studentInv->user_id = LoginUser::getUser()->id;
					$studentInv->is_active = 1;
					if (!$studentInv->save()){
						throw new Exception(json_encode($studentInv->getErrors()));
					}
					
					//create invoice detail monthly fee;
					$studentDInv = new MStudentDinvoice();
					$studentDInv->invoice_id = $studentInv->id;
					$studentDInv->student_item_id = 1; //monthly fee item id
					$studentDInv->notes = "Monthly Fee Period : " .$period;
					$studentDInv->total = $studentClass->monthly_fee;
					$studentDInv->disc = $studentClass->disc_monthly_fee;					
					$studentItem = MStudentItem::model()->findByPK(1);
					$studentDInv->disc_acc_id = $studentItem->disc_acc_id;
					$studentDInv->pendapatan_acc_id = $studentItem->pendapatan_acc_id;
					if (!$studentDInv->save()){
						throw new Exception(json_encode($studentDInv->getErrors()));
					}
					
					//create Jurnal detail untuk monthly fee
					Jurnal::createDetail($jurnalId, $studentInv->piutang_acc_id ,"", "D", $studentClass->monthly_fee - $studentClass->disc_monthly_fee);
					Jurnal::createDetail($jurnalId, $studentDInv->disc_acc_id ,"", "D", $studentClass->disc_monthly_fee);
					Jurnal::createDetail($jurnalId, $studentDInv->pendapatan_acc_id ,"", "C", $studentClass->monthly_fee);
					
					//Calculate late fee
					$prevPeriod = Utils::getPrevPeriod($period);
					$lastStudentMF = MStudentMonthlyFee::model()->find("end_period = :prevPeriod and student_class_id = :studentClassId ",array(":prevPeriod"=>$prevPeriod,":studentClassId"=>$studentClass->id));
					
					if ($lastStudentMF == null){
						$lateFee = 0;
					}else{
						$prevStudentInvId = $lastStudentMF->student_invoice_id;
						//get payment
						$maxLateFee = AppSetting::getValue("maxLateFee");
						
						$criteria = new CDbCriteria();
						$criteria->condition = "student_invoice_id =:studentInvId and studentPayment.is_active = 1";
						$criteria->order = "t.id desc";
						$criteria->params = array(":studentInvId"=>$prevStudentInvId);					
						$payment = MStudentDpayment::model()->with("studentPayment")->find($criteria);
						
						if($payment== null){						
							$lateFee = $maxLateFee;
						}else{						
							$lateFeePerDay = AppSetting::getValue("lateFeePerDay");						
							$maxDateMonthlyFee = AppSetting::getValue("maxDateMonthlyFee");
							$maxDateMonthlyFee = $prevPeriod ."-". $maxDateMonthlyFee;
							$maxDateMonthlyFee = new DateTime($maxDateMonthlyFee);
							$tglBayar = new DateTime($payment->studentPayment->payment_date);
							$interval = $maxDateMonthlyFee->diff($tglBayar);
							$diff = $interval->days;						
							if ($diff <= 0){
								$lateFee = 0;
							}else{
								$lateFee = $lateFeePerDay * $diff;
								if ($lateFee > $maxLateFee){
									$lateFee = $maxLateFee;
								}
							}						
						}
					}
					
					if ($lateFee > 0){
						//add Detail invoice
						$studentDInv = new MStudentDinvoice();
						$studentDInv->invoice_id = $studentInv->id;
						$studentDInv->student_item_id = 2; //item acc id untuk late fee
						$studentDInv->notes = "Late Fee";
						$studentDInv->total = $lateFee;
						$studentDInv->disc = 0;
						$studentItem = MStudentItem::model()->findByPK(2);
						$studentDInv->disc_acc_id = $studentItem->disc_acc_id;
						$studentDInv->pendapatan_acc_id = $studentItem->pendapatan_acc_id;
						if (!$studentDInv->save()){
							throw new Exception(json_encode($studentDInv->getErrors()));
						}
						//jurnal detail late fee					
						Jurnal::createDetail($jurnalId, $studentInv->piutang_acc_id ,"", "D", $lateFee);
						Jurnal::createDetail($jurnalId, $studentDInv->disc_acc_id ,"", "D", 0);
						Jurnal::createDetail($jurnalId, $studentDInv->pendapatan_acc_id ,"", "C",$lateFee);							
					}
										
					//update invoice header add total invoice & total disc
					$studentInv->ttl_invoice = $studentClass->monthly_fee + $lateFee;
					$studentInv->ttl_disc =  $studentClass->disc_monthly_fee;
										
					if(!$studentInv->save()){
						throw new Exception(json_encode($studentInv->getErrors()));
					}
					
					//generate student monthly fee
					$invoiceNo = AutoNumber::getNewStudentInvoiceNo($invoiceNo);
					$studentMF = new MStudentMonthlyFee();
					$studentMF->student_class_id = $studentClass->id;
					$studentMF->ttl_monthly_fee = $studentClass->monthly_fee;
					$studentMF->ttl_disc_fee = $studentClass->disc_monthly_fee;
					$studentMF->ttl_late_fee = $lateFee;
					$studentMF->start_period = $period;
					$studentMF->end_period = $period;
					$studentMF->student_invoice_id=$studentInv->id;
					if(!$studentMF->save()){
						throw new Exception(json_encode($studentMF->getErrors()));
					}
				}else{
					
					//skip monthly fee sudah ada;
				}
			}
		}catch (Exception $e){
			$transaction->rollback();
			$this->result["actionStatus"] =2;
			$this->result["actionMessage"] =$errorMessage;
			$this->result["actionError"] =$e->getMessage();
			$this->returnJSON($this->result);
		}
		
		$transaction->commit();
		$this->result["actionStatus"] =0;
		$this->result["actionMessage"] =Yii::t("dialog","Proses generate monthy fee selesai.");
		$this->result["actionError"] ="";
		$this->returnJSON($this->result);		
	}
	
	
	public function actionCancelGenerate(){
		set_time_limit(0);
		$period = Yii::app()->request->getParam("period","");
		$startDate = Utils::getPeriodStartDate($period);
		$errorMessage = Yii::t("dialog","Proses pembantalan monthy fee $period gagal !");
		$conn = Yii::app()->db;
		
		//cek apakah sudah ada payment terhadap invoice yang di generate secara automatis;
		$studentMonthlyFee = MStudentMonthlyFee::model()->with("studentInvoice")->find("start_period = :period and end_period =:period and (ttl_paid + ttl_cn) > 0  and studentInvoice.is_active = 1",array("period"=>$period));		
		if ($studentMonthlyFee != null){
			$this->result["actionStatus"] =2;
			$this->result["actionMessage"] =Yii::t("dialog","Proses generate monthy fee tidak dapat di batalkan, karena sudah ada pembayaran terhadap invoice yang di generate.");
			$this->result["actionError"] ="";
			$this->returnJSON($this->result);
		}
				
		$transaction = $conn->beginTransaction();
		try{
			$rs = MStudentMonthlyFee::model()->findAll("start_period =:period and end_period = :period",array(":period"=>$period));
			foreach ($rs as $rw){
				
				$studentInv = $rw->studentInvoice;
				
				Jurnal::deleteDetailJurnal($studentInv->jurnal_id,$conn);
				
				$ssql = "delete from m_student_dinvoice where invoice_id = :invoiceId";
				$cmd = $conn->createCommand($ssql);
				$cmd->execute(array(":invoiceId"=>$studentInv->id));
				
				//remove student_monthly fee
				if(!$rw->delete()){
					$this->result["actionStatus"] =2;
					$this->result["actionMessage"] =$errorMessage;
					$this->result["actionError"] =$rw->getErrors();
					$transaction->rollback();
					$this->returnJSON($this->result);
				}
				
				//remove invoice header 
				if(!$studentInv->delete()){
					$this->result["actionStatus"] =2;
					$this->result["actionMessage"] =$errorMessage;
				 	$this->result["actionError"] =$studentInv->getErrors();
				 	$transaction->rollback();
				 	$this->returnJSON($this->result);
				}
				
				Jurnal::deleteHeaderJurnal($studentInv->jurnal_id,$conn);				
			}
			$transaction->commit();
			$this->result["actionStatus"] =0;
			$this->result["actionMessage"] =Yii::t("dialog","monthy fee $period telah di batalkan.");
			$this->result["actionError"] ="";
			$this->returnJSON($this->result);
		}catch (Exception $e){
			$this->result["actionStatus"] =2;
			$this->result["actionMessage"] =$errorMessage;
			$this->result["actionError"] =$e->getMessage();
			$transaction->rollback();
			$this->returnJSON($this->result);
		}
		
	}
	
	public function actionGenerate_index(){
		
		$period =Utils::getPeriod(); 
		$data["period"] = $period;
		
		//department
		$arrDepartment = MDepartment::model()->findAll("is_active = 1");
		$data["arrDepartment"] = $arrDepartment;
		$department_id = $arrDepartment == null ? "" : $arrDepartment[0]->id;
		$data["department_id"] = $department_id;
		
		//$tahunAjaranActive = CMTahunAjaran::model()->find("department_id =:deptId and start_date <= :startDate and end_date >= :startDate",array("deptId"=>$department_id ,"startDate"=>Utils::getPeriodStartDate($period)));
		
		//tingkatan
		$ssql = "select distinct d.id as id,d.name as name from m_student_class a
				inner join m_kelas_tahun_ajaran b on a.kelas_tahun_ajaran_id = b.id
				inner join m_kelas c on b.kelas_id = c.id
				inner join m_tingkatan d on c.tingkatan_id = d.id
				inner join m_tahun_ajaran e on b.tahun_ajaran_id = e.id
				where
				d.department_id =:deptId and
				e.start_date <= :startDate and e.end_date >= :startDate";
		$conn = Yii::app()->db;
		$cmd = $conn->createCommand($ssql);
		$arrTingkatan = $cmd->query(array(":deptId"=>$department_id,":startDate"=>Utils::getPeriodStartDate($period)));
		$arrTingkatan = $arrTingkatan->readAll();
		$tingkatan_id= "";
		
		foreach($arrTingkatan as $tingkatan){
			$tingkatan_id = $tingkatan["id"];
			break;
		};
		$data["arrTingkatan"] = $arrTingkatan;		
		$data["tingkatan_id"] = $tingkatan_id;
		
		//Kelas tahun ajaran
		$ssql = "select distinct b.id as id,c.name as name from m_student_class a
				inner join m_kelas_tahun_ajaran b on a.kelas_tahun_ajaran_id = b.id
				inner join m_kelas c on b.kelas_id = c.id
				inner join m_tahun_ajaran e on b.tahun_ajaran_id = e.id
				where
				c.tingkatan_id =:tingkatanId and
				e.start_date <= :startDate and e.end_date >= :startDate";
		$conn = Yii::app()->db;
		$cmd = $conn->createCommand($ssql);
		$arrKelas = $cmd->query(array(":tingkatanId"=>$tingkatan_id,":startDate"=>Utils::getPeriodStartDate($period)));
		$arrKelas = $arrKelas->readAll();
		$kelas_tahun_ajaran_id = "";
		foreach($arrKelas as $kelas){
			$kelas_tahun_ajaran_id = $kelas["id"];
			break;
		};
		$data["arrKelas"] = $arrKelas;
		$data["kelas_tahun_ajaran_id"] = $kelas_tahun_ajaran_id ;
		
		
		$this->render('generate_index',$data);		
	}
	public function actionGenerateDetailList(){
		$department_id = Yii::app()->request->getParam("department_id","");
		$kelas_tahun_ajaran_id = Yii::app()->request->getParam("kelas_tahun_ajaran_id","");
		$period = Yii::app()->request->getParam("period","");
		
		$ssql = "select c.id as student_id,c.sid,c.name as student_name,g.name as tingkatan_name,f.name as kelas_name,a.*,d.invoice_no,d.ttl_paid,d.ttl_cn from m_student_monthly_fee a
				inner join m_student_class b on a.student_class_id = b.id
				inner join m_student c on b.student_id = c.id
				inner join m_student_hinvoice d on a.student_invoice_id = d.id
				inner join m_kelas_tahun_ajaran e on b.kelas_tahun_ajaran_id = e.id
				inner join m_kelas f on e.kelas_id = f.id
				inner join m_tingkatan g on f.tingkatan_id = g.id
				where
				g.department_id =:deptId and a.start_period <= :period and a.end_period >= :period order by a.id desc";
		
		$conn = Yii::app()->db;
		$cmd = $conn->createCommand($ssql);
		$arrResult = $cmd->query(array(":deptId"=>$department_id,":period"=>$period));
		$arrResult = $arrResult->readAll();
		
		$this->result["actionStatus"] =0;
		$this->result["actionMessage"] ="";
		$this->result["actionError"] =array();
		$this->result["model"] = $arrResult;
		$this->returnJSON($this->result);
	
	}
}
?>